rm(list=ls())

#-------------------------------------#
# APPENDIX MAYORS POLICING LATAM      #
#                                     #
# JESSICA ZARKIN NOTNI                #
# JUNE 2024                           #
#-------------------------------------#


##---------------- STEP 1: PACKAGES ####


library(tidyr)
library(tibble)
library(stargazer)
library(dplyr)
library(haven)
library(readstata13)
library(ggplot2)
library(zoo)
library(ggridges)
library(ggpubr)
library(ggplotify)
library(readxl)



##---------------- STEP 2: LOAD DATA SETS ####

cdmx <- read.dta13("6_Replication/1_Data/A_CDMX_FigA16.dta")
cdmx_ensu <- read_excel("6_Replication/1_Data/A_CDMX_FigA7.xlsx")
cdmx_cityensu <- read_excel("6_Replication/1_Data/A_CDMX_FigA6.xlsx")


sp <- read_excel("6_Replication/1_Data/A_SP_FigA14.xlsx")
fm <- read_excel("6_Replication/1_Data/A_SP_FigA15.xlsx")
spsurv <- read_excel("6_Replication/1_Data/A_SP_FigA4.xlsx")
cidsurv <- read_excel("6_Replication/1_Data/A_SP_FigA5.xlsx")


car <- read_excel("6_Replication/1_Data/A_COL_FigA9.xlsx")
bog <- read_excel("6_Replication/1_Data/A_COL_FigA8.xlsx")
colo <- read.dta13("6_Replication/1_Data//A_COL_FigA10_A11_A12.dta")
  colo100plus <- subset(colo, city100==1)
colsurv <- read_excel("6_Replication/1_Data/A_COL_FigA3.xlsx")  
private <- read_excel("6_Replication/1_Data/A_COL_FigA13.xlsx")  

latamregion<- read_excel("6_Replication/1_Data/A_LATAM_FigA1.xlsx") 
latam<- read_excel("6_Replication/1_Data/A_LATAM_FigA2.xlsx") 

##-------------------- STEP 3: CREATE FIGURE A1   ####

region<-ggplot(latamregion, aes(x=grouping, y=pcseguridad)) +
  geom_line() + 
  scale_x_continuous(name="Year", 
                     breaks = c(1,2,3,4,5,6,7,8,9,10,11), 
                     labels = c("2004/2005", "2006/2007", "2008/2009",
                                "2010/2011","2012/2013","2014","2016",
                                "2017/2018","2019","2021","2023")) + 
  scale_y_continuous(name="Percentage", limits=c(0, 100))

plot(region)

ggsave("4_Figures/CPS/Region_All.png", plot=region, width=9,height=5, units = "in", dpi = 500)


##-------------------- STEP 4: CREATE FIGURE A2   ####

countries<-ggplot(latam, aes(x=year, y=pcseguridad)) +
  geom_line() + 
  scale_x_continuous(name="Year", limits=c(2004, 2023)) + 
  scale_y_continuous(name="Percentage", limits=c(0, 100)) +
  facet_wrap(~country, ncol = 3)

plot(countries)

ggsave("4_Figures/CPS/Region_Countries.png", plot=countries, width=11,height=9, units = "in", dpi = 500)



##-------------------- STEP 5: CREATE FIGURE A3   ####

colcities<-ggplot(colsurv, aes(x=year, y=Percentage, color=Question)) +
  geom_line(linewidth=1.2) + 
  scale_x_continuous(name="Year", breaks=c(2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020, 2021, 2022)) +
  scale_y_continuous(name="Percentage", limits=c(0,100)) +
  scale_color_brewer(palette="Dark2") +
  facet_wrap(~city, ncol = 4)

plot(colcities)

ggsave("4_Figures/CPS/COL_SurveyCity.png", plot=colcities, width=17,height=10, units = "in", dpi = 500)

##---------------- STEP 6: CREATE FIGURE A4 ####

spcesop<-ggplot(spsurv, aes(x=year, y=perc)) +
  geom_line() + 
  scale_x_continuous(name="Year", limits=c(1991,2020)) + 
  scale_y_continuous(name="Percentage", limits=c(0,100))

plot(spcesop)

ggsave("4_Figures/CPS/SP_CESOP.png", plot=spcesop, width=12,height=8, units = "in", dpi = 500)

##---------------- STEP 7: CREATE FIGURE A5. ####

cidsurv<-subset(cidsurv, cidsurv$Year==2000)

cidop<-ggplot(data=cidsurv, aes(x=Municipality, y=Percentage)) +
  geom_bar(stat="identity")

plot(cidop)

ggsave("4_Figures/CPS/Cidades_CESOP.png", plot=cidop, width=13,height=8, units = "in", dpi = 500)

##-------------------- STEP 8: CREATE FIGURE A6   ####

ensucitycdmx<-ggplot(cdmx_cityensu, aes(x=month, y=percentage, color=Question)) +
  geom_line(linewidth=1.2) + 
  scale_x_continuous(breaks = 1:27, name="Month_Year", 
                     labels = paste0(c("03_17", "09_17","09_17", "12_17",
                                       "03_18", "09_18","09_18", "12_18",
                                       "03_19", "09_19","09_19", "12_19", 
                                       "03_20", "09_20", "12_20", 
                                       "03_21", "06_21", "09_21", "12_21", 
                                       "03_22", "06_22", "09_22", "12_22", 
                                       "03_23", "06_23", "09_23", "12_23"))) +
  scale_y_continuous(name="Percentage", limits=c(0,100)) +
  theme(axis.text.x = element_text(angle=90)) +
  scale_color_brewer(palette="Dark2")

plot(ensucitycdmx)

ggsave("4_Figures/CPS/CDMX_ENSUCity.png", plot=ensucitycdmx, width=11,height=8, units = "in", dpi = 500)

##-------------------- STEP 9: CREATE FIGURE A7   ####

ensucdmx<-ggplot(cdmx_ensu, aes(x=numfecha, y=problemadelincuencia)) +
  geom_line() + 
  scale_x_continuous(breaks = 1:17, name="Month_Year", 
                     labels = paste0(c("09_19", "12_19", "03_20", "09_20", "12_20", "03_21", "06_21", 
                                       "09_21", "12_21", "03_22", "06_22", "09_22", "12_22", "03_23",
                                       "06_23", "09_23", "12_23"))) +
  scale_y_continuous(name="Percentage",limits=c(0,100)) +
  facet_wrap(~alcaldia, ncol = 4) +
  theme(axis.text.x = element_text(angle=90))

plot(ensucdmx)

ggsave("4_Figures/CPS/CDMX_ENSUBoroughs.png", plot=ensucdmx, width=13,height=8, units = "in", dpi = 500)

##---------------- STEP 10: CREATE FIGURE A8. ####

bogtrends<-ggplot(bog, aes(x=year, y=rate)) +
  geom_line() + 
  scale_x_continuous(name="Year", breaks=c(2003, 2005, 2007, 2009, 2011, 2013, 2015, 2017, 2019, 2021)) + 
  scale_y_continuous(name="Rate") +
  facet_wrap(~type, ncol = 2, scales = "free")

plot(bogtrends)

ggsave("4_Figures/CPS/BOG_Trends.png", plot=bogtrends, width=10,height=5, units = "in", dpi = 500)


##---------------- STEP 11: CREATE FIGURE A9. ####

cartrends<-ggplot(car, aes(x=year, y=rate)) +
  geom_line() + 
  scale_x_continuous(name="Year", breaks=c(2003, 2005, 2007, 2009, 2011, 2013, 2015, 2017, 2019, 2021)) + 
  scale_y_continuous(name="Rate") +
  facet_wrap(~type, ncol = 2, scales = "free")

plot(cartrends)

ggsave("4_Figures/CPS/CAR_Trends.png", plot=cartrends, width=10,height=5, units = "in", dpi = 500)

##-------------------- STEP 12: CREATE FIGURE A10   ####


auto100 <- ggplot(colo100plus, aes(x=rate_hurto_auto, y=comp_fonsetpcdol, label=namesh_mpio)) + 
  geom_point(alpha = 0.8) +
  geom_text(size = 1.5,vjust = -1.5) +
  ylab("Average FONSET expenditures, 13-20 (USD pc)") + 
  xlab("Average vehicle theft, 13-19") +  
  geom_smooth(method=lm, colour="black") + 
  theme(legend.position = "bottom",
        panel.background = element_rect(fill = "#f7f7f7",colour = "grey50"),
        axis.text.x = element_text(size = 8),
        axis.text.y = element_text(size = 8),
        axis.title.x = element_text(size =10),
        axis.title.y = element_text(size =10)) 


plot(auto100)

ggsave("4_Figures/CPS/Appendix_Figure 4 (using vehicle theft).png", plot=auto100, width=7,height=5, units = "in", dpi = 500)



##-------------------- STEP 13: CREATE FIGURE A11   ####


homloess <- ggplot(colo100plus, aes(x=rate_homicidios, y=comp_fonsetpcdol, label=namesh_mpio)) + 
  geom_point(alpha = 0.8) +
  ylab("Average FONSET expenditures, 13-20 (USD pc)") + 
  xlab("Average homicide rate, 13-19") +  
  geom_smooth(method=loess, colour="black") + 
  theme(legend.position = "bottom",
        panel.background = element_rect(fill = "#f7f7f7",colour = "grey50"),
        axis.text.x = element_text(size = 8),
        axis.text.y = element_text(size = 8),
        axis.title.x = element_text(size =10),
        axis.title.y = element_text(size =10)) +
  geom_text(size = 1.5,vjust = -1.5)


plot(homloess)

ggsave("4_Figures/CPS/Appendix_Figure 4_LOESS.png", plot=homloess, width=7,height=5, units = "in", dpi = 500)


##-------------------- STEP 14: CREATE FIGURE A12   ####


panela_noout <- ggplot(subset(colo100plus, y_corr_tributpcdol<2000), aes(x=y_corr_tributpcdol, y=comp_fonsetpcdol, label=namesh_mpio)) + 
  geom_point(alpha = 0.8) +
  geom_text(size = 1.5,vjust = -1.5) +
  geom_smooth(method=lm, colour="black") + 
  ylab("Average FONSET expenditure, 13-20 (USD pc)") + 
  xlab("Average tax collection, 13-20 (USD pc)") +  
  theme(legend.position = "bottom",
        panel.background = element_rect(fill = "#f7f7f7",colour = "grey50"),
        axis.text.x = element_text(size = 8),
        axis.text.y = element_text(size = 8),
        axis.title.x = element_text(size =10),
        axis.title.y = element_text(size =10)) 


plot(panela_noout)


panelb_noout <- ggplot(subset(colo100plus, y_corr_tributpcdol<2000), aes(x=y_corr_tributpcdol, y=negpcejecfonset, label=namesh_mpio)) + 
  geom_point(alpha = 0.8) +
  geom_text(size = 1.5,vjust = -1.5) +
  geom_smooth(method=lm, colour="black") + 
  ylab("Average % FONSET budget unspent (13-20)") + 
  xlab("Average tax collection, 13-20 (USD pc)") +  
  theme(legend.position = "bottom",
        panel.background = element_rect(fill = "#f7f7f7",colour = "grey50"),
        axis.text.x = element_text(size = 8),
        axis.text.y = element_text(size = 8),
        axis.title.x = element_text(size =10),
        axis.title.y = element_text(size =10)) 


plot(panelb_noout)

panels<-ggarrange(panela_noout, panelb_noout, ncol=2, nrow=1)

plot(panels)

ggsave("4_Figures/CPS/Appendix_Figure5_RedoNoOutliers.png", plot=panels, width=9,height=5, units = "in", dpi = 500)


##---------------- STEP 15: CREATE FIGURE A13. ####


priv<-ggplot(data=private, aes(x=Jurisdiction, y=Rate)) +
  geom_bar(stat="identity")

plot(priv)

ggsave("4_Figures/CPS/COL_PrivateSec.png", plot=priv, width=10,height=6, units = "in", dpi = 500)

##---------------- STEP 16: CREATE FIGURE A14. ####

sptrends<-ggplot(sp, aes(x=year, y=rate)) +
  geom_line() + 
  scale_x_continuous(name="Year", limits=c(2001, 2022)) + 
  scale_y_continuous(name="Rate") +
  facet_wrap(~type, ncol = 2, scales = "free")

plot(sptrends)

ggsave("4_Figures/CPS/SP_Trends.png", plot=sptrends, width=10,height=5, units = "in", dpi = 500)

##---------------- STEP 17: CREATE FIGURE A15. ####

fmtrends<-ggplot(fm, aes(x=year, y=rate)) +
  geom_line() + 
  scale_x_continuous(name="Year", limits=c(2001, 2022)) + 
  scale_y_continuous(name="Rate") +
  facet_wrap(~type, ncol = 2, scales = "free")

plot(fmtrends)

ggsave("4_Figures/CPS/FM_Trends.png", plot=fmtrends, width=10,height=5, units = "in", dpi = 500)

##---------------- STEP 18: CREATE FIGURE A16 ####

robberycdmx<-ggplot(cdmx, aes(x=year, y=tdel_roboveh)) +
  geom_line() + 
  scale_x_continuous(name="Year", limits=c(2016, 2022)) + 
  scale_y_continuous(name="Vehicle Theft") +
  facet_wrap(~where, ncol = 4) +
  geom_vline(xintercept=2018) 

plot(robberycdmx)

ggsave("4_Figures/CPS/CDMX_VehicleTheftTrends.png", plot=robberycdmx, width=12,height=8, units = "in", dpi = 500)

